<?php
	define('CONFIG_FILE',true);
	include '../config.php';
	
	define('DB_FILE',true);
	require_once '../Tool/DB/SqlDBManager.class.php';

	session_start();
	if (empty($_SESSION["userType"])) {
		$url="../Index.php";
		include_once '../Tool/Common/Redirect.php';
		exit();
	}

	if($_SESSION["userType"]=="Therapy Assistant"){
        $url="../Index.php";
		include_once '../Tool/Common/Redirect.php';
		exit();
    }
    
	$sqlDBManager = new SqlDBManager();

	$page = isset($_POST['page']) ? intval($_POST['page']) : 1;
	$rows = isset($_POST['rows']) ? intval($_POST['rows']) : 10;
	$sort = isset($_POST['sort']) ? strval($_POST['sort']) : 'Username';
	$order = isset($_POST['order']) ? strval($_POST['order']) : 'asc';
	$searchid='';
	if(isset($_POST['searchid'])){
		$searchid = $_POST['searchid'];
	}
	$offset = ($page - 1) * $rows;
	$result = array();

	if (!empty($_SESSION["username"])) {
	    $username = $_SESSION["username"];
	}

	if (!empty($_SESSION["userType"])) {
	    $userType = $_SESSION["userType"];
	}

	if (!empty($_SESSION["centreName"])) {
	    $centreName = $_SESSION["centreName"];
	}
	if ($userType == "System Admin") {		
		//Filter the $searchid
		$searchid=addslashes($searchid);
		$searchid=str_replace("%", "\%", $searchid);
		$searchid=str_replace("_", "\_", $searchid);
		
		//Count
	    $sql ="SELECT COUNT( * ) 
			FROM user, centre
			WHERE user.CentreId IS NOT NULL 
			AND user.CentreId = centre.CentreId
			AND (
			Username LIKE  ?
			OR UserType LIKE  ?
			OR StaffName LIKE  ?
			OR ContactNumber LIKE  ?
			OR CentreName LIKE  ?
			OR Email LIKE  ?
			)";			

		$parameters = array("%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%");
		$res=$sqlDBManager->queryRow($sql,$parameters);
		if(!empty($res)){
			$result["total"] = $res[0];
		}
		else{
			$result["total"] = 0;
		}

		$sql ="SELECT COUNT( * ) 
			FROM user
			WHERE CentreId IS NULL 
			AND (
			Username LIKE  ?
			OR UserType LIKE  ?
			OR StaffName LIKE  ?
			OR ContactNumber LIKE  ?
			OR Email LIKE  ?
			)";			

		$parameters = array("%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%");
		$res=$sqlDBManager->queryRow($sql,$parameters);
		if(!empty($res)){
			$result["total"] += $res[0];
		}
		else{
			$result["total"] += 0;
		}
		
		//SELECT 
		$sql = "SELECT Username, UserType, StaffName, ContactNumber, Email, CentreName
			FROM user, centre
			WHERE user.CentreId IS NOT NULL 
			AND user.CentreId = centre.CentreId
			AND (
			Username LIKE  ?
			OR UserType LIKE  ?
			OR StaffName LIKE  ?
			OR ContactNumber LIKE  ?
			OR CentreName LIKE ?
			OR Email LIKE  ?
			) 
			UNION 
			SELECT Username, UserType, StaffName, ContactNumber, Email, 'NULL' AS CentreName
			FROM user
			WHERE CentreId IS NULL 
			AND (
			Username LIKE  ?
			OR UserType LIKE  ?
			OR StaffName LIKE  ?
			OR ContactNumber LIKE  ?
			OR Email LIKE  ?
			)
			order by $sort $order 
			limit $offset,$rows";
		

		$parameters = array("%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%");
		$res=$sqlDBManager->queryRows($sql,$parameters);
		$items1 = array();
		
		if(!empty($res)){
			for($i=0;$i<count($res);$i++)
			{
				array_push($items1, $res[$i]);
			}	
		}
	 	$result["rows"] =$items1;
	    echo json_encode($result);

	}else{
		//Filter the $searchid
		$searchid=addslashes($searchid);
		$searchid=str_replace("%", "\%", $searchid);
		$searchid=str_replace("_", "\_", $searchid);
			
	    $sql ="SELECT COUNT( * ) 
			FROM user, centre
			WHERE centre.CentreName =?
			AND user.CentreId = centre.CentreId
			AND (
			Username LIKE ?
			OR UserType LIKE  ?
			OR StaffName LIKE  ?
			OR ContactNumber LIKE  ?
			OR Email LIKE  ?
			)";
	    
		$parametesql = array($centreName,"%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%");
		$res=$sqlDBManager->queryRow($sql,$parametesql);
		if(!empty($res)){
			$result["total"] = $res[0];
		}
		else{
			$result["total"] = 0;
		}
		
		$sql ="SELECT Username, UserType, StaffName, ContactNumber, Email, CentreName
			FROM user, centre
			WHERE centre.CentreName =?
			AND user.CentreId = centre.CentreId
			AND (
			Username LIKE  ?
			OR UserType LIKE ?
			OR StaffName LIKE  ?
			OR ContactNumber LIKE ?
			OR Email LIKE  ?
			) order by $sort $order 
			limit $offset,$rows";
		
		$parametesql = array($centreName,"%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%");
		$res=$sqlDBManager->queryRows($sql,$parametesql);
		$items = array();
		
		if(!empty($res)){
			for($i=0;$i<count($res);$i++)
			{
				if($res[$i]['UserType']=='Therapy Assistant' || $res[$i]['Username']==$username){
					array_push($items, $res[$i]);
				}			
			}	
		}
		
		//close connection
  		$sqlDBManager->close_connect();

  	    $result["rows"] = $items;
  	    echo json_encode($result);
	}
?>